<?php
/**
 * Created by PhpStorm.
 * User: happy
 * Date: 17/11/8
 * Time: 上午8:56
 * arrango数据库的查询和操作
 */
class Model2
{
    protected  static $record=false;//是否开始记录，决定alter_sql和cancel_sql是否生效，且清空之前的记录
    protected  static $last_sql;//最后一条sql语句
    protected  static $alter_sql;//所有更新语句，包括插入删除和修改
    protected  static  $cancel_sql;//所有可撤销的语句，对alter_sql语句的逆向

    protected $Item = 'u';
    protected $Collection_name = '';

    protected $Filter = '';
    protected $Returns = 'u';

    protected $Condition = array('limit' => '', 'order' => '', 'group' => '');
    protected $Is_preview = false;//是否是sql预览
    protected $Is_prefixreplace = false;//是否替换#


    //设置链接
    public  static  function  set_link($database='_system',$prefix='',$ENDPOINT='tcp://127.0.0.1:8529',$user='root',$pass='',$options=array()){
        if(is_array($database)){
            plugin('arango')->Link_arr=$database;
        }
        else{
            $basic_config=array(
                'database'=>$database,
                'db_prefix'=>$prefix,
                'endpoint'=>$ENDPOINT,
                'user'=>$user,
                'pass'=>$pass
            );
            if($options){$basic_config=array_merge($basic_config,$options);}
            plugin('arango')->Link_arr=$basic_config;
        }
    }


    //构造函数
    public function __construct($name = '')
    {
        $this->Collection_name =plugin('arango')->Link_arr['db_prefix'].$name;
    }

    //查询操作
    public function  query($sql)
    {
        return plugin('arango')->query($this->format_sql($sql));
    }

    //执行操作
    public  function   execute($sql)
    {
        return MYSQL::execute($this->format_sql($sql));//不是搜索的查询
    }


    public static  function beginTransaction()//开始事务
    {
        MYSQL::beginTransaction();
    }

    public  static function record(){
        self::$record=true;
        self::$cancel_sql='';
        self::$alter_sql='';
    }
    //开始记录，记录后alter和cancel才开始生效
    public  static function start_record(){
        self::$record=true;
    }

    //获取记录
    public  static  function get_record(){
        return array(
            'cancel'=>self::$cancel_sql,
            'alter'=>self::$alter_sql
        );
    }
    //添加取消语句
    public  static  function  add_cancel($sql){
        self::$cancel_sql.=';'.$sql;
    }
    //清空记录
    public  static  function  clear_record(){
        self::$cancel_sql='';
        self::$alter_sql='';
    }
    //也是开始事务的同名函数，同一个意思
    public static  function begin()//开始事务
    {
        MYSQL::beginTransaction();
    }

    public static  function commit()
    {
        MYSQL::commit();
    }

    //执行多条sql语句，语句以分号间隔,会以事务方式提交,一条失败不会提交
    public  static  function  multiSql($sql){
        $sql_arr=explode(';',$sql);
        // $result=[];
        self::begin();
        foreach($sql_arr as $value){
            $value=trim($value); //去除空格造成的影响
            if($value){
                MYSQL::execute($value);//执行语句
            }
        }
        self::commit();
        return 'ok';
    }


    //回滚
    public  static  function  rollback(){
        MYSQL::rollback();
    }

    //遍历的值
    public   function  item($item='u'){
         $this->Item=$item;
        return $this;
    }
    //选择
    public   function  select($sql = '')
    {
        if ($sql) {
            //直接使用和执行

        } else {
            $sql = sprintf("FOR %s  IN  %s ",$this->Item,$this->Collection_name);

            if ($this->Filter) {//如果有where
                $sql .= ' FILTER ' . $this->Filter;
            }

            //先group 分组
            if ($this->Condition['collect']) {
                $sql .= ' COLLECT  ' . $this->Condition['collect'];
            }

            if ($this->Condition['sort']) {
                $sql .= ' SORT ' . $this->Condition['sort'];
            }

            if ($this->Condition['limit']) {
                $sql .= ' LIMIT ' . $this->Condition['limit'];
            }

            if($this->Returns){
                $sql.=' RETURN '.$this->Returns;
            }
        }

        $sql=$this->format_sql($sql);

        if ($this->Is_preview) {//如果是预览直接返回sql语句
            Model2::$last_sql = $sql;
            return $sql;
        } else {
            Model2::$last_sql = $sql;
            return (array)plugin('arango')->query($sql);//不是搜索的查询
        }
    }






    /**仅查询第一条数据*/
    public function  find()
    {
        $ret=$this->limit(1)->select();
        if($this->Is_preview){
            return $ret;
        }
       else  if(is_array($ret)){
           return $ret[0];
       }
        else {
          return $ret;
        }

    }

    //数目查询
    public function  count($sql = '')
    {
        $this->collect('WITH COUNT INTO length');
        $this->Returns = 'length';
        $result = $this->select($sql);
        if ($this->Is_preview) {
            return $result;
        } else {
           return intval($result[0]);
            //return intval($result[0]['num']);
        }
    }
    //将字符串里的#替换为表前缀
    private  function  format_sql($sql){
        if($this->Is_prefixreplace){
            $prefix=plugin('arango')->Link_arr['db_prefix'];
            return str_replace('#',$prefix,$sql);
        }
        else{
            return $sql;
        }

    }
    //是否对字符串中的#号进行前缀替换
    public function prefix_replace($flag=true)
    {
        $this->Is_prefixreplace =$flag;
        return $this;
    }
   /*
    * FOR u IN users
        FILTER u.status == "not active"
                 UPDATE u WITH { status: "inactive" } IN users
    * */
    public function  update($update)
    {
        if (!$update) {
            return false;
        } else if (is_string($update)) {
            $str = $update;
            $sql = sprintf("UPDATE %s WITH %s IN  %s RETURN OLD", $this->Item,$str,$this->Collection_name);
        } else if (is_array($update)) {
            $str = json_encode($update);
           $sql = sprintf("UPDATE %s WITH %s IN  %s RETURN OLD", $this->Item,$str,$this->Collection_name);
        } else {
            return false;
        }

        $sql_part = sprintf("FOR %s  IN  %s ",$this->Item,$this->Collection_name);
        if ($this->Filter) {//如果有where
            $sql_part .= ' FILTER ' . $this->Filter;
        }
        $sql=$sql_part.' '.$sql;

        if ($this->Is_preview) {//如果是预览直接返回sql语句
            return $sql;
        } else {
            Model2::$last_sql = $sql;
            if(self::$record){//如果执行记录
                self::$alter_sql.=';'.$sql;
            }
            return plugin('arango')->execute($this->format_sql($sql));//不是搜索的查询
        }
    }


    //支持数组的方式array('name'=>'u.name','age'=>'u.age') 或者直接'name,age'或者返回一维数组 'name'
    public  function  returns($returns){
        if(is_array($returns)){
             $arr=array();
           foreach($returns as $key=> $value){
               $arr[]=sprintf("\"%s\":%s ",$key,$value);
           }
            $str= implode(',',$arr);
            $str='{'.$str.'}';
            $returns=$str;
        }
        else{
            if(substr($returns,0,1)=='{'||$returns=='u'){
                //直接不用改变

            }
            else { //自行组装
                $field_arr=explode(',',$returns);
                $item=$this->Item;
                if(count($field_arr)==1){//单个字段
                   //添加前缀
                    $field_tmp=$field_arr[0];

                    if(strstr($field_tmp,':')){
                        $key_pair=explode(':',$field_tmp);
                        $new_field=array($key_pair[1]=>$item.'.'.$key_pair[0]);
                        return $this->returns($new_field);//以数组的方式处理
                    }else{
                        $returns=$item.'.'.$field_arr[0];
                    }
                }
                else{
                    $new_field=array();
                    foreach($field_arr as $value){
                        if(strstr($value,':')){
                             $key_pair=explode(':',$value);
                            $new_field[$key_pair[1]]=$item.'.'.$key_pair[0];
                        }
                        else{
                            $new_field[$value]=$item.'.'.$value;
                        }

                    }
                    return $this->returns($new_field);//以数组的方式处理
                }
            }
        }
        $this->Returns = $returns;
        return $this;
    }

    public function  delete()
    {
        $sql=sprintf("REMOVE %s IN %s RETURN OLD",$this->Item,$this->Collection_name);

        $sql_part = sprintf("FOR %s  IN  %s ",$this->Item,$this->Collection_name);
        if ($this->Filter) {//如果有where
            $sql_part .= ' FILTER ' . $this->Filter;
        }
        $sql=$sql_part.' '.$sql;

        if ($this->Is_preview) {//如果是预览直接返回sql语句
            return $sql;
        } else {
            Model2::$last_sql = $sql;
            if(self::$record){//如果执行记录
                self::$alter_sql.=';'.$sql;
            }
            return plugin('arango')->execute($this->format_sql($sql));//不是搜索的查询
        }
    }

    public function  insert($insert)
    {
        //$result=false;
        if (is_array($insert)) { //数组拼接执行
            $insert=json_encode($insert);

            $sql = sprintf("INSERT  %s IN  %s RETURN  NEW",$insert, $this->Collection_name);
            if ($this->Is_preview) {//如果是预览直接返回sql语句
                return $sql;
            } else {
                Model2::$last_sql = $sql;
                if(self::$record){//如果执行记录
                    self::$alter_sql.=';'.$sql;
                }
                $result= plugin('arango')->execute($sql);
            }
        }
        else {//字符串直接执行
            $result= plugin('arango')->execute($insert);//不是搜索的查询
        }
        return $result;
    }

    public function  getLastId()
    {
        return MYSQL::getLastId();
    }

//----------------------
    public function  table($name)
    {
        $prefix=plugin('arango')->Link_arr['db_prefix'];

        $this->Collection_name=$prefix . $name;//加上表前缀
        return $this;
    }

    public function preview()
    {
        $this->Is_preview = true;
        return $this;
    }
   //条件筛选 支持array('u.name'=>'xiaohong');或者array('namme'=>'xiaohong')或者'u.name=="xiaohong"'
    public   function  filter($condition){
        if (!$condition) {
            $this->Filter = ' u._key > 0 '; //默认选中全部
        } else if (is_string($condition)) {
            $this->Filter=$condition;
        } else if (is_array($condition)) {
            //如果是多维数组则返回错误信息
            $str = '';
            foreach ($condition as $key => $value) {
                if(is_array($value)){ //条件必须是一维数组
                    throw new Exception(" Model2 sql condition must be One-dimensional array");
                }

                if(strstr($key,'PRE')){//如果键值包含PRE，直接连接value
                    $str.=$value;continue;//下一次循环
                }

                //链接两个条件默认用and
                $value_arr=explode(' ',$key);
                if (in_array('&&' ,$value_arr) || in_array('||',$value_arr)||substr($str,strlen($str)-1,1)==='(') {
                    //如果存在直接往下执行
                    if(in_array('&&' ,$value_arr)){
                        $key=$value_arr[1].$value_arr[2];//&& aa LIKE=>
                        $str.=' '.$value_arr[0].' ';
                    }
                    else if(in_array('||',$value_arr)){
                        $key=$value_arr[1].' '.$value_arr[2];
                        $str.=' '.$value_arr[0].' ';
                    }

                } else {
                    $str .= ' &&  ';
                }

                if(!strstr($key,'.')){
                        $key=$this->Item.'.'.$key;
                }
                //$key中存在这些条件
                if (strstr($key, '==')||strstr($key, '!=') || strstr($key, '<') || strstr($key, '>') || in_array('LIKE',$value_arr)|| in_array('IN',$value_arr)) {

                    if (in_array('IN',$value_arr)) { //如果包含in方法
                       $value="'".str_replace(',',"','",$value)."'";//添加引号 lcc 2015-9-13
                        $str .= " $key [$value]";
                    }
                    else if (in_array('LIKE',$value_arr)) { //如果包含like方法

                        $str .= " $key \"%$value%\"";
                    }
                    else if(is_numeric($value)){
                        $str .= " $key $value";
                    }
                    else{
                        $str .= " $key '$value'";
                    }
                }

                else if (is_numeric($value)&&$key!='u._key') {//_key是字符串,看起来像数字
                    $str .= ' ' . $key . '==' . $value;
                }

                else if(is_string($value)){
                    $str .= " $key==\"$value\"";
                }
                else if(is_numeric($value)){//数字的等于要区别处理
                    $str .= " $key==$value";
                }
            }
            //去掉开头的and
            if(substr($str,0,3)==' &&') $str = substr($str, 3);
            $this->Filter = $str;

        } else {//默认操作
            $this->Filter = '1=2'; //不执行任何语句
        }

        return $this;
    }

    public function  limit($limit, $start = 0)
    {
        if ($limit) {
            $this->Condition['limit'] = $start ? "$start ,$limit" : $limit;
        }
        return $this;
    }

    public function  sort($sort)
    {
        if ($sort) {
            $this->Condition['sort'] = $sort;
        }
        return $this;
    }
    //分组 group的替换  collect
    public  function  collect($collect){
        $this->Condition['collect'] = $collect;
        return $this;
    }

    //过滤字段，防止sql注入
    public function  filter_key($key)
    {
        if (!preg_match('/^[A-Z_\|\&\-.a-z0-9]+$/', trim($key))) {//只接收大小写和数字字符串
            write('非法sql注入:' . $key);
            throw_exception("illegal sql非法注入");
            exit(0);
        } else {
            return $key;
        }
    }

    public static function  get_last_sql()
    {
        return Model2::$last_sql;
    }
    public  static  function  M($name=''){
        return new Model2($name);
    }
    public  static  function  get_last_id(){
        return MYSQL::getLastId();
    }

    //添加获取数据表列表 主键  字段列表的功能
    //获取所有数据库中所有表
    public  static  function  get_table_list(){
        $sql="SELECT table_name FROM information_schema. TABLES WHERE table_schema = '".MYSQL::$Link_arr['db_name']."' AND table_type = 'base table'";
        $tables= MYSQL::query($sql);
        $new_arr=array();
        foreach($tables as $key=>$value){
            $new_arr[$key]=$value['table_name'];
        }
        return $new_arr;
    }
    //获取表所有字段  三种类型，0 返回原型 1 返回精简数组 2 返回字符串 ,最后一个参数决定是否使用表前缀
    public  static  function  get_field($table_name='',$type=''){
        $sql='SHOW COLUMNS FROM  '.$table_name;
        $columns=MYSQL::query($sql);
        if($type==1){//是否精简化
            $column=array();
            foreach($columns as $value){
                array_push($column,$value['Field']);
            }
            $columns=$column;
        }
        else if($type==2){//是否精简化
            $column='';
            foreach($columns as $value){
                $column.=','.$value['Field'];
            }
            $columns=substr($column,1);
        }
        return $columns;
    }
    //
}

function Model2($table=''){
    return new Model2($table);
}